Export/Import Apex applications in SQL Developer

Using SQL Developer to export or import an Apex application is not only easy, it’s also fast!

Export

To export, follow these steps:

Open the connection to the schema where you’re application is located and search for the “Application Express” directory:

image_1

Right click on the application you want to export and choose “Quick DDL” > “Save to File…”:

image_2

When done, you’ll have the same file as you would have exported from the Apex GUI.

 

Import

To import an application, right click on the “Application Express” directory, and choose “Import Application”. You can also right click on an existing application; the next steps are the same.

Follow the wizard. When this screen appears, you can choose to do a number things:

image_3

 

  • Auto Assign Application ID
  • Use the application ID from the export
  • Specify New ID

When the ID that you want to use is already assigned in your workspace, don’t forget to check the “Overwrite” checkbox.

At the end of the wizard you see the progress of the installation:

image_4

When finished, you’re application is up and running.

Read from anydata column

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more Continue reading

Search database objects with a variable in oracle

Something I really often use is this select:

SELECT distinct type,line,name,text
FROM user_source
WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%’)
UNION
SELECT distinct ‘column’ type,null line,table_name||’.’|| COLUMN_NAME name,null
FROM user_tab_columns
WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%’)
UNION
SELECT  DISTINCT ‘table’ type,null line,object_name name,null
from user_objects
WHERE object_type IN  (‘TABLE’,’VIEW’)
AND lower(object_name) LIKE(‘%’||lower(:search_source)||’%’)
ORDER BY type,name,line

This select will show you most of the database objects in a schema(columns, table,view,code), where this ‘search_source’ string is used.

You can add this into you sqldeveloper as well, as a user defined report.
To accomplish this you will have to do the following steps:
1) Open sqldeveloper
2) go to Reports tab(If you don’t see this tab go to the Menu choose view->Reports
3) right-click ‘User Defined Reports’ -> add Report -> give name and put the sql code as above into SQL input screen(you can leave the rest as default)
You can use this Search in sqldeveloper now.

SQL Developer and ApEx: Application missing

Last week I was searching for a long time to find my application in SQL Developer. I wanted to deploy it to a production environment, the application had APP_ID 113 but I couldn’t find it in SQL Developer:

After some time I noticed an other application that had the same name: “Daily Report”. After I changed the name of my application to “Daily Reports”, I could see it in SQL Developer and deploy it!