Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Introduction to Python for PL/SQL Developers - Full Series

unknown-1040115Aug 8 2016 — edited Aug 28 2017

Learning Python for PL/SQL Developers

A tutorial series that presents an easier way to learn Python by comparing and contrasting to PL/SQL.

by Arup Nanda, Oracle ACE Director

Learning a new language can be challenging. Working a full-time job and using precious downtime to learn makes this only tougher. But you have to learn to keep pace with technological advances. The Python language is worth learning due to its use in growing areas such as machine learning and data analytics. If you are a PL/SQL developer, you might be very interested in learning it but probably struggle with carving out time to actually do that. What is the best way to jump start learning?

If you are a PL/SQL developer, you already know that language very well and you know how to write a program; you just can't write it in Python, yet. Instead of starting out from the beginning, as with a grammar book, my objective is to teach you Python by showing how it is the same as or different from PL/SQL. That guarantees a faster adoption. Once the roots are established, you are free to move onto more intricate details of the language.

How This Series Is Organized

Each article has a summary of important points and finally short quiz to test your understanding. The quizzes are also available to download as PDF documents that you can take with you. I encourage you to check out the videos in addition to the articles.

Introduction to Python for PL/SQL Developers

Part 1: Introduction

Full Article ~ Quiz

https://www.youtube.com/watch?v=WoAVY7LQbt4

Part 2: Conditions and Loops

                      [Full Article](/docs/DOC-1007066)  ~  [Quiz](/docs/DOC-1003587)

https://www.youtube.com/watch?v=7HE6XbB6Y2Q

**Part 3: Collections and Arrays
**

Full Article ~ Quiz

https://www.youtube.com/watch?v=SyDAlj0w02s

**Part 4: Functions, Modules and File Operations
**

                      [Full Article](/docs/DOC-1007382)  ~  [Quiz](/docs/DOC-1007386)

https://www.youtube.com/watch?v=-u8IGOpr6q0

**Part 5: Data Manipulations and Oracle Connectivity
**

                                       ~  [Full Article](/docs/DOC-1007383)  ~

https://www.youtube.com/watch?v=rTnuZ8LbTX0

Be sure to Follow the Oracle Database Community page to get the most recent news and articles posted across the Oracle Database product and technology spaces.

Click the "Follow" button at the top right of the page.

Stay Connected on Social:

| ic-Facebook-red.png | ic-Twitter-red.png | ic-Youtube-red.png | ic-Blog-red.png |

Comments

odie_63
PS: I don't want to use an hardcoding
Why?

Do you have such a scenario that you don't know the procedure name you're trying to compile?

Edited by: odie_63 on 11 janv. 2013 10:45
padders
Simple answer is you can't - without generating some additional metadata for the package.

One way to generate the metadata would appear to be PL/Scope in 11g but I haven't actually tried that to see how it pans out.
odie_63
There's $$PLSQL_UNIT compiler directive but it doesn't reference subprograms in a package, only the package itself :
SQL> create or replace package mypck is
  2    procedure myprc;
  3  end;
  4  /
 
Package created
 
SQL> 
SQL> create or replace package body mypck is
  2    procedure myprc is
  3    begin
  4      dbms_output.put_line('I''m in '||$$PLSQL_UNIT);
  5    end;
  6  end;
  7  /
 
Package body created
 
SQL> exec mypck.myprc;
 
I'm in MYPCK
 
PL/SQL procedure successfully completed
 
bencol
This might help you understand the problem:
http://tkyte.blogspot.co.uk/2009/10/httpasktomoraclecomtkytewhocalledme.html

Buit I usually hardcode the proc name into a variable and use that for logging.
unknown-7404
You've posted enough to know that you need to provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
>
is it possible to obtain a called procedure/function name within package?

For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
>
I usually use this method

1. Create a SQL type for logging information
2. Put the package name into a constant in the package spec
3. Add a line to each procedure/function for the name.

Sample package spec
	/******************************************************************************
	 * Constants and package variables
	 ******************************************************************************/
		gc_pk_name			CONSTANT VARCHAR2(30) := 'PK_TEST';
Sample procedure code in package
	 PROCEDURE P_TEST_INIT
	 IS
	   c_proc_name CONSTANT VARCHAR2(80)  := 'P_TEST_INIT';
	   v_log_info  TYPE_LOG_INFO := TYPE_LOG_INFO(gc_pk_name, c_proc_name); -- create the log type instance

	 BEGIN
		NULL; -- code goes here
	 EXCEPTION
	 WHEN ??? THEN
		v_log_info.log_code := SQLCODE;  -- add info to the log type
		v_log_info.log_message := SQLERRM;
		v_log_info.log_time    := SYSDATE;
		pk_log.p_log_error(v_log_info);
                                raise;
	 END P_PK_TEST_INIT;
Sample SQL type
DROP TYPE TYPE_LOG_INFO;

CREATE OR REPLACE TYPE TYPE_LOG_INFO AUTHID DEFINER AS OBJECT (
/* *****************************************************************************
 *  NAME:      TYPE_LOG_INFO
 *  PURPOSE:   Holds info used by PK_LOG package to log errors.
 *
 *             Using a TYPE instance keeps the procedures and functions
 *             independent of the logging mechanism.
 *
 *             If new logging features are needed a SUB TYPE can be derived
 *             from this base type to add the new functionality without
 *             breaking any existing code.
 *
 *  REVISIONS:
 *  Ver        Date        Author           Description
 *
 *   1.00      mm/dd/yyyy  me               Initial Version.
 ******************************************************************************/
    PACKAGE_NAME  VARCHAR2(80),
    PROC_NAME     VARCHAR2(80),
    STEP_NUMBER   NUMBER,
    LOG_LEVEL   VARCHAR2(10),
    LOG_CODE    NUMBER,
    LOG_MESSAGE VARCHAR2(1024),
    LOG_TIME    TIMESTAMP,
    CONSTRUCTOR FUNCTION type_log_info (p_package_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_proc_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_step_number IN NUMBER DEFAULT 1,
                                        p_LOG_level IN VARCHAR2 DEFAULT 'Uninit',
                                        p_LOG_code IN NUMBER DEFAULT -1,
                                        p_LOG_message IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_LOG_time IN DATE DEFAULT SYSDATE)
                RETURN SELF AS RESULT
  ) NOT FINAL;
/

DROP TYPE BODY TYPE_LOG_INFO;

CREATE OR REPLACE TYPE BODY TYPE_LOG_INFO IS
    CONSTRUCTOR FUNCTION type_log_info (p_package_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_proc_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_step_number IN NUMBER DEFAULT 1,
                                        p_LOG_level IN VARCHAR2 DEFAULT 'Uninit',
                                        p_LOG_code IN NUMBER DEFAULT -1,
                                        p_LOG_message IN VARCHAR2 DEFAULT 'Uninitialized',
                                        p_LOG_time IN DATE DEFAULT SYSDATE)
     RETURN SELF AS RESULT IS
    BEGIN
      self.package_name  := p_package_name;
      self.proc_name     := p_proc_name;
      self.step_number   := p_step_number;
      self.LOG_level   := p_LOG_level;
      self.LOG_code    := p_LOG_code;
      self.LOG_message := p_LOG_message;
      self.LOG_time    := p_LOG_time;
      RETURN;
    END;
END;
/
SHO ERR
Edited by: rp0428 on Jan 11, 2013 10:35 AM after 1st cup of coffee ;)
odie_63
rp0428 wrote:
You would want the $$PLSQL_UNIT directive.
And as shown in my post, this doesn't apply to subprograms in a package, which seems to be precisely what OP is after.
unknown-7404
Thanks - brain f*** - didn't even see your post somehow. I update my post with the old method.
1 - 7

Post Details

Added on Aug 8 2016
8 comments
51,770 views